﻿using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Reflection;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using up7.db.database;
using up7.db.sql.model;
using up7.db.utils;
using up7.filemgr.app;

namespace up7.db.sql
{
    public class SqlTable
    {
        /// <summary>
        /// 数据表名称
        /// </summary>
        public string name;

        private JToken m_obj;
        /// <summary>
        /// 所有字段
        /// </summary>
        private List<SqlParam> m_fields;
        private Dictionary<string, SqlParam> m_fieldsDic;
        private SqlParam m_pk = null;//主键

        public static SqlTable build(string name)
        {
            return new SqlTable(name);
        }

        public SqlTable(string name)
        {
            this.name = name;
            this.m_fields = new List<SqlParam>();
            this.m_fieldsDic = new Dictionary<string, SqlParam>();

            ConfigReader cr = new ConfigReader();
            this.m_obj = cr.module(string.Format("database.{0}", name));

            var dbType = ConfigReader.dbType();
            //加载所有字段
            var fs = this.m_obj.SelectToken("fields");
            foreach (var o in fs)
            {
                SqlParam p = new SqlParam(dbType);
                p.Name = o["name"].ToString().Trim();
                p.Type = o["type"].ToString().Trim();
                p.length = int.Parse(o["length"].ToString().Trim());
                p.primary = bool.Parse(o["primary"].ToString());
                p.identity = bool.Parse(o["identity"].ToString());
                if (string.Compare(p.Type, "string", true) == 0) p.DbType = DbType.String;
                else if (string.Compare(p.Type, "int", true) == 0) p.DbType = DbType.Int32;
                else if (string.Compare(p.Type, "long", true) == 0) p.DbType = DbType.Int64;
                else if (string.Compare(p.Type, "bool", true) == 0) p.DbType = DbType.Boolean;
                else if (string.Compare(p.Type, "DateTime", true) == 0) p.DbType = DbType.DateTime;
                if (p.primary) this.m_pk = p;
                this.m_fields.Add(p);
                this.m_fieldsDic.Add(p.Name, p);
            }
        }

        public SqlParam primaryKey()
        {
            return this.m_pk;
        }

        /// <summary>
        /// 所有字段
        /// </summary>
        /// <returns></returns>
        public SqlParam[] all()
        {
            return this.m_fields.ToArray();
        }

        public Dictionary<string, SqlParam> allDic()
        {
            return this.m_fieldsDic;
        }

        /// <summary>
        /// 获取指定字段
        /// </summary>
        /// <param name="names"></param>
        /// <returns></returns>
        public SqlParam[] sel(string names)
        {
            var all = this.all();
            //
            Dictionary<string, bool> dic = new Dictionary<string, bool>();
            foreach (var n in names.Split(',')) dic.Add(n.Trim(), true);

            List<SqlParam> sp = new List<SqlParam>();
            foreach (var f in all)
            {
                if (dic.ContainsKey(f.Name)) sp.Add(f);
            }
            return sp.ToArray();
        }

        /// <summary>
        /// 合并变量值
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        public SqlParam[] mergeVal(SqlParam[] sp)
        {
            List<SqlParam> ps = new List<SqlParam>();
            var dic = this.allDic();
            foreach (var p in sp)
            {
                if (dic.ContainsKey(p.Name))
                {
                    dic[p.Name].m_valTm = p.m_valTm;
                    dic[p.Name].m_valDecimal = p.m_valDecimal;
                    dic[p.Name].m_valStr = p.m_valStr;
                    dic[p.Name].m_valLong = p.m_valLong;
                    dic[p.Name].m_valBool = p.m_valBool;
                    dic[p.Name].m_valInt = p.m_valInt;
                    dic[p.Name].m_valByte = p.m_valByte;
                    dic[p.Name].m_valDouble = p.m_valDouble;
                    ps.Add(dic[p.Name]);
                }
            }
            return ps.ToArray();
        }

        /// <summary>
        /// 创建变量并将值绑定到cmd
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="ps"></param>
        public void bind(DbCommand cmd, SqlParam[] ps)
        {
            var vals = this.mergeVal(ps);

            foreach (var v in vals)
            {
                v.bind(cmd);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="o">需要实现DataBaseAttribute，与字段绑定</param>
        public void insert(object o)
        {
            //提取所有字段和值（忽略自增字段）
            var fields = this.extractFields(o);
            var sql = SqlBuilder.build().insert(this.name, fields);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //将变量值绑定到cmd
            this.bind(cmd, fields);
            var id = db.ExecuteScalar(cmd);
        }

        public void inserts<T>(T[] arr)
        {
            if (arr.Length == 0) return;
            //提取所有字段和值（忽略自增字段）
            var fields = this.extractFields(arr[0]);
            var sql = SqlBuilder.build().insert(this.name, fields);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //将变量值绑定到cmd
            this.bind(cmd, fields);
            cmd.Connection.Open();
            cmd.Prepare();
            foreach (var a in arr)
            {
                var pars = this.extractFields(a);
                int i = 0;
                foreach (var p in pars)
                {
                    cmd.Parameters[i++].Value = p.Value;
                }
                cmd.ExecuteNonQuery();
            }
            cmd.Connection.Close();
            cmd.Dispose();
        }

        public void inserts<T>(List<T> arr)
        {
            if (arr.Count == 0) return;
            //提取所有字段和值（忽略自增字段）
            var fields = this.extractFields(arr[0]);
            var sql = SqlBuilder.build().insert(this.name, fields);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //将变量值绑定到cmd
            this.bind(cmd, fields);
            cmd.Connection.Open();
            cmd.Prepare();
            foreach (var a in arr)
            {
                var pars = this.extractFields(a);
                int i = 0;
                foreach (var p in pars)
                {
                    cmd.Parameters[i++].Value = p.Value;
                }
                cmd.ExecuteNonQuery();
            }
            cmd.Connection.Close();
            cmd.Dispose();
        }

        public void update(object o, SqlWhere where)
        {
            //提取所有字段和值
            var fields = this.extractFields(o);
            var sql = SqlBuilder.build().update(this.name, fields, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定更新变量
            this.bind(cmd, fields);
            //绑定条件变量
            where.bind(this, cmd);
            db.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 更新指定的数据列
        /// </summary>
        /// <param name="o"></param>
        /// <param name="columns"></param>
        /// <param name="where"></param>
        public void update(object o, string columns, SqlWhere where)
        {
            //提取所有字段和值
            var all = this.extractFields(o, columns);
            var sql = SqlBuilder.build().update(this.name, all, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定更新变量
            this.bind(cmd, all);
            //绑定条件变量
            where.bind(this, cmd);
            db.ExecuteNonQuery(cmd);
        }

        public void update(object o, string columns, string condition)
        {
            //提取更新列
            var all = this.extractFields(o, columns);
            //提取条件列
            var cds = this.extractFields(o, condition);
            var sql = SqlBuilder.build().update(this.name, all, cds);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定更新变量
            this.bind(cmd, all);
            //绑定条件变量
            this.bind(cmd, cds);
            db.ExecuteNonQuery(cmd);
        }

        public void update(object o, SqlParam[] where)
        {
            //提取所有字段和值
            var fields = this.extractFields(o);
            var sql = SqlBuilder.build().update(this.name, fields, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定更新变量
            this.bind(cmd, fields);
            //绑定条件变量
            this.bind(cmd, where);
            var id = db.ExecuteScalar(cmd);
        }
        public void update(SqlSeter st, SqlParam[] where)
        {
            var sql = SqlBuilder.build().update(this.name, st, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定更新变量
            this.bind(cmd, st.toArray());
            //绑定条件变量
            this.bind(cmd, where);
            db.ExecuteNonQuery(cmd);
        }
        public void update(SqlSeter st, SqlWhere where)
        {
            var sql = SqlBuilder.build().update(this.name, st, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定更新变量
            this.bind(cmd, st.toArray());
            //绑定条件变量
            where.bind(this, cmd);
            var id = db.ExecuteScalar(cmd);
        }
        /// <summary>
        /// 根据主键更新所有字段
        /// </summary>
        /// <param name="o"></param>
        public void update(object o)
        {
            //提取所有字段和值
            var fields = this.extractWithoutPk(o);
            var pk = new SqlParam[] { this.m_pk };
            var sql = SqlBuilder.build().update(this.name, fields, pk);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定更新变量
            this.bind(cmd, fields);
            //绑定条件变量
            pk[0].bind(cmd);
            var id = db.ExecuteScalar(cmd);
        }

        /// <summary>
        /// 根据指定条件批量更新指定列数据
        /// </summary>
        /// <param name="arr"></param>
        /// <param name="updateNames"></param>
        /// <param name="whereNames"></param>
        public void updates<T>(List<T> arr, string updateNames, string whereNames)
        {
            if (arr.Count == 0) return;

            //提取需要更新的字段和值
            var colsUpdate = this.sel(updateNames);
            var colsWhere = this.sel(whereNames);
            var sql = SqlBuilder.build().update(this.name, colsUpdate, colsWhere);
            Dictionary<string, int> indexs = new Dictionary<string, int>();
            foreach (var col in colsUpdate) indexs.Add(col.Name, indexs.Count);
            foreach (var col in colsWhere) indexs.Add(col.Name, indexs.Count);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //将变量值绑定到cmd
            this.bind(cmd, colsUpdate);
            this.bind(cmd, colsWhere);
            cmd.Connection.Open();
            cmd.Prepare();
            foreach (var a in arr)
            {
                var pars = this.extractFields(a, updateNames + "," + whereNames);
                foreach (var p in pars)
                {
                    cmd.Parameters[indexs[p.Name]].Value = p.Value;
                }
                cmd.ExecuteNonQuery();
            }
            cmd.Connection.Close();
            cmd.Dispose();
        }

        /// <summary>
        /// 批量将对象字段更新指定的值
        /// </summary>
        /// <param name="arr"></param>
        /// <param name="st"></param>
        /// <param name="whereNames"></param>
        public void updates<T>(List<T> arr, SqlSeter st, string whereNames)
        {
            if (arr.Count == 0) return;

            var colsWhere = this.sel(whereNames);
            var sql = SqlBuilder.build().update(this.name, st, colsWhere);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //将变量值绑定到cmd
            st.bind(this, cmd);
            this.bind(cmd, colsWhere);
            cmd.Connection.Open();
            cmd.Prepare();
            foreach (var a in arr)
            {
                var pars = this.extractFields(a, whereNames);
                int i = st.size();
                foreach (var p in pars)
                {
                    cmd.Parameters[i++].Value = p.Value;
                }
                cmd.ExecuteNonQuery();
            }
            cmd.Connection.Close();
            cmd.Dispose();
        }

        /// <summary>
        /// 从数据表中读取数据并自动赋值至对象成员中
        /// 对象成员通过DataBaseAttribute与数据表字段关联
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        public T read<T>(SqlParam[] where)
        {
            var o = default(T);
            //提取所有字段和值
            var fields = this.extractFields(o, false);
            var sql = SqlBuilder.build().select(this.name, fields, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定更新变量
            this.bind(cmd, fields);
            //绑定条件变量
            this.bind(cmd, where);
            var r = db.ExecuteReader(cmd);
            if (r.Read())
            {
                SqlValueSeter.build().read(r, fields, o);
            }
            return o;
        }

        /// <summary>
        /// 从数据表中读取数据并自动赋值至对象成员中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        public T read<T>(SqlWhere where) where T : new()
        {
            var o = new T();
            //提取所有字段和值
            var fields = this.extractFields(o, false);
            var sql = SqlBuilder.build().select(this.name, fields, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定条件变量
            where.bind(this, cmd);
            var r = db.ExecuteReader(cmd);
            if (r.Read())
            {
                SqlValueSeter.build().read(r, fields, o);
            }
            else { o = default(T); }
            r.Close();
            return o;
        }

        public T readOne<T>(SqlWhere w) where T : new()
        {
            var o = new T();
            //提取所有字段和值
            var fields = this.extractFields(o, false);
            var sql = SqlBuilder.build().select(this.name, fields, w, 1);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定条件变量
            w.bind(this, cmd);
            var r = db.ExecuteReader(cmd);
            if (r.Read())
            {
                SqlValueSeter.build().read(r, fields, o);
            }
            else { o = default(T); }
            r.Close();
            return o;
        }

        public T readOne<T>(string cols, SqlWhere w) where T : new()
        {
            var o = new T();
            //提取所有字段和值
            var columns = this.sel(cols);
            var sql = SqlBuilder.build().select(this.name, columns, w, 1);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定条件变量
            w.bind(this, cmd);
            var r = db.ExecuteReader(cmd);
            if (r.Read())
            {
                SqlValueSeter.build().read(r, columns, o);
            }
            else { o = default(T); }
            r.Close();
            return o;
        }

        public List<T> reads<T>(SqlWhere where) where T : new()
        {
            var o = new T();
            List<T> arr = new List<T>();
            //提取所有字段和值
            var fields = this.extractFields(o, false);
            var sql = SqlBuilder.build().select(this.name, fields, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定条件变量
            where.bind(this, cmd);
            var r = db.ExecuteReader(cmd);
            while (r.Read())
            {
                var item = new T();
                SqlValueSeter.build().read(r, fields, item);
                arr.Add(item);
            }
            r.Close();
            return arr;
        }

        public List<T> reads<T>(SqlWhere where, SqlSort sort) where T : new()
        {
            var o = new T();
            List<T> arr = new List<T>();
            //提取所有字段和值
            var fields = this.extractFields(o, false);
            var sql = SqlBuilder.build().select(this.name, fields, where, sort);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定条件变量
            where.bind(this, cmd);
            var r = db.ExecuteReader(cmd);
            var st = SqlValueSeter.build();
            while (r.Read())
            {
                var item = new T();
                st.read(r, fields, item);
                arr.Add(item);
            }
            r.Close();
            return arr;
        }

        public List<T> reads<T>(string cols, SqlWhere where, SqlSort sort) where T : new()
        {
            var o = new T();
            List<T> arr = new List<T>();
            //提取所有字段和值
            var fields = this.sel(cols);
            var sql = SqlBuilder.build().select(this.name, fields, where, sort);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定条件变量
            where.bind(this, cmd);
            var r = db.ExecuteReader(cmd);
            var st = SqlValueSeter.build();
            while (r.Read())
            {
                var item = new T();
                st.read(r, fields, item);
                arr.Add(item);
            }
            r.Close();
            return arr;
        }

        public void delete(SqlParam[] where)
        {
            //提取所有字段和值
            var sql = SqlBuilder.build().delete(this.name, where);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定条件变量
            this.bind(cmd, where);
            var id = db.ExecuteScalar(cmd);
        }

        public void delete(SqlWhere w)
        {
            //提取所有字段和值
            var sql = SqlBuilder.build().delete(this.name, w);

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            //绑定条件变量
            w.bind(this, cmd);
            var id = db.ExecuteScalar(cmd);
        }

        public void clear()
        {
            string sql = "delete from " + this.name;
            if (ConfigReader.dbType() == DataBaseType.Oracle)
                sql = "truncate table " + this.name;

            DbHelper db = new DbHelper();
            var cmd = db.GetCommand(sql);
            db.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 从对象中提取与数据表绑定的字段列表
        /// 同时将对象值绑定到SQL字段中
        /// </summary>
        /// <param name="o"></param>
        /// <param name="igoIdentity">忽略自增字段</param>
        /// <returns></returns>
        private SqlParam[] extractFields(object o, bool igoIdentity = true)
        {
            List<SqlParam> fields = new List<SqlParam>();

            //遍历所有成员
            foreach (var m in o.GetType().GetFields())
            {
                //未实现绑定特性
                if (!m.IsDefined(typeof(DataBaseAttribute))) continue;
                var attr = (DataBaseAttribute)m.GetCustomAttribute(typeof(DataBaseAttribute));
                //查找关联字段
                if (this.m_fieldsDic.ContainsKey(attr.name))
                {
                    var f = this.m_fieldsDic[attr.name];
                    f.Value = m.GetValue(o);
                    //忽略自增字段
                    if (igoIdentity && f.identity) continue;
                    fields.Add(f);
                }
            }
            return fields.ToArray();
        }

        /// <summary>
        /// 从对象中提取指定数据列
        /// </summary>
        /// <param name="o"></param>
        /// <param name="names">指定的数据列名称</param>
        /// <returns></returns>
        private SqlParam[] extractFields(object o, string names)
        {
            var sels = this.sel(names);
            Dictionary<string, SqlParam> dic = new Dictionary<string, SqlParam>();
            foreach (var s in sels) dic.Add(s.Name, s);

            List<SqlParam> fields = new List<SqlParam>();

            //遍历所有成员
            foreach (var m in o.GetType().GetFields())
            {
                //未实现绑定特性
                if (!m.IsDefined(typeof(DataBaseAttribute))) continue;
                var attr = (DataBaseAttribute)m.GetCustomAttribute(typeof(DataBaseAttribute));
                //查找关联字段
                if (dic.ContainsKey(attr.name))
                {
                    var f = dic[attr.name];
                    f.Value = m.GetValue(o);
                    fields.Add(f);
                }
            }
            return fields.ToArray();
        }

        /// <summary>
        /// 获取除主键外的所有字段
        /// </summary>
        /// <returns></returns>
        private SqlParam[] extractWithoutPk(object o)
        {
            List<SqlParam> fields = new List<SqlParam>();

            //遍历所有成员
            foreach (var m in o.GetType().GetFields())
            {
                //未实现绑定特性
                if (!m.IsDefined(typeof(DataBaseAttribute))) continue;
                var attr = (DataBaseAttribute)m.GetCustomAttribute(typeof(DataBaseAttribute));
                //查找关联字段
                if (this.m_fieldsDic.ContainsKey(attr.name))
                {
                    var f = this.m_fieldsDic[attr.name];
                    f.Value = m.GetValue(o);
                    //忽略主键
                    if (f.primary) continue;
                    fields.Add(f);
                }
            }
            return fields.ToArray();
        }
    }
}